package cn.edu.nju.software.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Queue;
import java.util.LinkedList;

import javax.naming.NamingException;
import javax.sql.DataSource;
import javax.swing.tree.DefaultMutableTreeNode;

import cn.edu.nju.software.bean.storage.Category;
import cn.edu.nju.software.dao.intf.CategoryDao;

public class CategoryDaoImpl implements CategoryDao {

	private DataSource ds;

	public CategoryDaoImpl() {
		try {
			ds = JDBCUtil.getDataSource();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public int addCategory(Category category) throws SQLException {
		// TODO Auto-generated method stub
		int result = -1;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("INSERT INTO category (name, father_id) "
							+ "VALUES (?, ?)",
							Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, category.getName());
			pstmt.setInt(2, category.getFatherID());
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				result = rs.getInt(1);
			}
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_FK_CONSTRAINT) {
				result = -1;
			} else if (errorCode == JDBCUtil.VIOLATION_OF_UQ_CONSTRAINT) {
				result = -2;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public boolean removeCategory(int categoryID) throws SQLException {
		// TODO Auto-generated method stub
		boolean result = true;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("DELETE FROM category WHERE id = ?");
			pstmt.setInt(1, categoryID);
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_FK_CONSTRAINT) {
				result = false;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public boolean updateCategoryName(Category category) throws SQLException {
		// TODO Auto-generated method stub
		boolean result = true;
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE category SET name = ? WHERE id = ?");
			pstmt.setString(1, category.getName());
			pstmt.setInt(2, category.getCategoryID());
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_UQ_CONSTRAINT) {
				result = false;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public DefaultMutableTreeNode getAllSubCategories(Category category)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DefaultMutableTreeNode root = new DefaultMutableTreeNode(category);
		Queue<DefaultMutableTreeNode> queue = new LinkedList<DefaultMutableTreeNode>();
		queue.add(root);
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT id, name FROM category WHERE father_id = ?");
			while (!queue.isEmpty()) {
				DefaultMutableTreeNode node = queue.remove();
				Category catg = (Category) node.getUserObject();
				pstmt.setInt(1, catg.getCategoryID());
				rs = pstmt.executeQuery();
				while (rs.next()) {
					DefaultMutableTreeNode nNode = new DefaultMutableTreeNode(
							new Category(rs.getInt(1), rs.getString(2),
									catg.getCategoryID()));
					node.add(nNode);
					queue.add(nNode);
				}
				rs.close();
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return root;
	}

	@Override
	public Category getCategoryByID(int id) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Category category = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT name, father_id FROM category WHERE id = ?");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				category = new Category(id, rs.getString(1), rs.getInt(2));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return category;
	}

}
